package net.solvetheriddle.wwclient.database;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import net.solvetheriddle.wwclient.WWLogic;
import net.solvetheriddle.wwclient.entity.Project;
import net.solvetheriddle.wwclient.entity.Work;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * @author anoniim
 * 
 */
public class WWDatabaseManager {

  private static final String WWCLIENT = "WWCLIENT";
  private static final int PROJECT_OPEN = 1;
  private static final String DB_ERROR = "! Exception while accessing database: ";
  private final String DB_NAME = "wwclient";
  private final int DB_VERSION = 1;

  // database names
  private static final String TABLE_PROJECTS = "projects";
  private static final String TABLE_WORKS = "works";
  private static final String TABLE_NOT_SENT_WORKS = "not_sent_works";
  private static final String TABLE_ROW_ID = "id";
  private static final String TABLE_ROW_PROJECT_ID = "project_id";
  private static final String TABLE_ROW_DATE_FROM = "date_from";
  private static final String TABLE_ROW_DATE_TO = "date_to";
  private static final String TABLE_ROW_NAME = "name";
  private static final String TABLE_ROW_OPEN = "open";
  private static final String TABLE_ROW_DESCRIPTION = "popis";

  private SQLiteDatabase db;
  private CustomSQLiteOpenHelper helper;

  public WWDatabaseManager(Context context) {
    helper = new CustomSQLiteOpenHelper(context);
  }

  public void open() {
    this.db = helper.getWritableDatabase();
  }

  public void close() {
    db.close();
  }

  public void addProject(Project project) {
    // add row
    ContentValues values = new ContentValues();
    values.put(TABLE_ROW_ID, project.getId());
    values.put(TABLE_ROW_NAME, project.getName());
    values.put(TABLE_ROW_OPEN, project.isOpen() ? 1 : 0);

    // insert the new data
    try {
      db.insert(TABLE_PROJECTS, null, values);
      Log.d(WWCLIENT, "Inserted project: " + project.toString());
    } catch (Exception e) {
      Log.e(WWCLIENT, DB_ERROR + e.toString());
      e.printStackTrace();
    }
  }

  public int deleteProject(long projectID) {
    try {
      int rowsAffected = db.delete(TABLE_PROJECTS, TABLE_ROW_ID + "=" + projectID, null);
      Log.d(WWCLIENT, "Deleted project with id: " + projectID);
      return rowsAffected;
    } catch (Exception e) {
      Log.e(WWCLIENT, DB_ERROR + e.toString());
      e.printStackTrace();
    }
    return 0;
  }

  public int deleteAllProjects() {
    try {
      int rowsAffected = db.delete(TABLE_PROJECTS, null, null);
      Log.d(WWCLIENT, "Deleted all projects");
      return rowsAffected;
    } catch (Exception e) {
      Log.e(WWCLIENT, DB_ERROR + e.toString());
      e.printStackTrace();
    }
    return 0;
  }

  public int deleteAllWorks() {
    try {
      int rowsAffected = db.delete(TABLE_WORKS, null, null);
      Log.d(WWCLIENT, "Deleted all works");
      return rowsAffected;
    } catch (Exception e) {
      Log.e(WWCLIENT, DB_ERROR + e.toString());
      e.printStackTrace();
    }
    return 0;
  }

  public Project getProject(long projectID) {
    Project project = null;
    Cursor cursor;

    try {
      cursor = db.query(TABLE_PROJECTS, new String[] { TABLE_ROW_ID, TABLE_ROW_NAME, TABLE_ROW_OPEN }, TABLE_ROW_ID + "=" + projectID, null, null, null, null, null);

      cursor.moveToFirst();
      if (!cursor.isAfterLast()) {
        do {
          project = new Project(cursor.getLong(0), cursor.getString(1), cursor.getInt(2) != 0);
        } while (cursor.moveToNext());
      }
      cursor.close();
    } catch (SQLException e) {
      Log.e(WWCLIENT, DB_ERROR + e.toString());
      e.printStackTrace();
    }
    Log.d(WWCLIENT, "$ Project " + project.toString() + " loaded from database");
    return project;
  }

  public List<Project> getAllProjects() {
    List<Project> projects = new ArrayList<Project>();
    Cursor cursor;

    try {
      cursor = db.query(TABLE_PROJECTS, new String[] { TABLE_ROW_ID, TABLE_ROW_NAME, TABLE_ROW_OPEN }, null, null, null, null, null, null);

      cursor.moveToFirst();
      if (!cursor.isAfterLast()) {
        do {
          Project project = new Project(cursor.getLong(0), cursor.getString(1), cursor.getInt(2) != 0);
          projects.add(project);
          Log.d(WWCLIENT, "$ Project " + project.toString() + " loaded from database");
        } while (cursor.moveToNext());
      }
      cursor.close();
    } catch (SQLException e) {
      Log.e(WWCLIENT, DB_ERROR + e.toString());
      e.printStackTrace();
    }
    Log.d(WWCLIENT, "$ ... " + projects.size() + " open projects loaded from database");
    return projects;
  }

  public List<Project> getOpenProjects() {
    List<Project> projects = new ArrayList<Project>();
    Cursor cursor;

    try {
      cursor = db.query(TABLE_PROJECTS, new String[] { TABLE_ROW_ID, TABLE_ROW_NAME, TABLE_ROW_OPEN }, TABLE_ROW_OPEN + "=" + PROJECT_OPEN, null, null, null, null, null);
      cursor.moveToFirst();
      if (!cursor.isAfterLast()) {
        do {
          Project project = new Project(cursor.getLong(0), cursor.getString(1), cursor.getInt(2) != 0);
          projects.add(project);
          Log.d(WWCLIENT, "$ Opened project " + project.toString() + " loaded from database");
        } while (cursor.moveToNext());
      }
      cursor.close();
    } catch (SQLException e) {
      Log.e(WWCLIENT, DB_ERROR + e.toString());
      e.printStackTrace();
    }
    Log.d(WWCLIENT, "$ ... " + projects.size() + " open projects loaded from database");
    return projects;
  }

  public long addNewWork(Work newWork) {
    ContentValues values = new ContentValues();
    values.put(TABLE_ROW_PROJECT_ID, newWork.getProjectID());
    values.put(TABLE_ROW_DATE_FROM, newWork.getFrom()/1000);

    try {
      long rowId = db.insert(TABLE_NOT_SENT_WORKS, null, values);
      Log.d(WWCLIENT, "$ " + newWork.toString() + " saved in database");
      return rowId;
    } catch (Exception e) {
      Log.e(WWCLIENT, DB_ERROR + e.toString());
      e.printStackTrace();
    }
    return 0;
  }

  public int updateWork(Work finishedWork) {
    ContentValues values = new ContentValues();
    values.put(TABLE_ROW_DESCRIPTION, finishedWork.getDescription());
    values.put(TABLE_ROW_DATE_TO, finishedWork.getTo()/1000);

    try {
      int returnCode = db.update(TABLE_NOT_SENT_WORKS, values, TABLE_ROW_ID + "=" + (int) finishedWork.getId(), null);
      Log.d(WWCLIENT, "$ Finished " + finishedWork.toString() + " updated in database");
      return returnCode;
    } catch (Exception e) {
      Log.e(WWCLIENT, DB_ERROR + e.toString());
      e.printStackTrace();
    }
    return 0;
  }

  /**
   * @return Currently running work (where endTime == 0) loaded from the
   *         database. Null when no such work is in found in the database.
   */
  public Work getUnfinishedWork() {
    Work work = null;
    Cursor cursor;

    try {
      cursor = db.query(TABLE_NOT_SENT_WORKS, new String[] { TABLE_ROW_ID, TABLE_ROW_PROJECT_ID, TABLE_ROW_DATE_FROM }, TABLE_ROW_DATE_TO + "=" + 0, null, null, null, null, null);
      cursor.moveToFirst();
      if (!cursor.isAfterLast()) {
        do {
          work = new Work(cursor.getLong(0), cursor.getLong(1), cursor.getLong(2)*1000);
        } while (cursor.moveToNext());
      }
      cursor.close();
    } catch (SQLException e) {
      Log.e(WWCLIENT, DB_ERROR + e.toString());
      e.printStackTrace();
    }
    if (work != null) {
      Log.d(WWCLIENT, "$ Unfinished " + work.toString() + " loaded from database");
    } else {
      Log.d(WWCLIENT, "$ No unfinished work found in database");
    }
    return work;
  }

  /**
     * 
     */
  public List<Work> getWorks(long projectID) {
    List<Work> works = new ArrayList<Work>();
    Cursor cursor;
    try {
      // get works from NOT SENT queue
      cursor = db.query(TABLE_NOT_SENT_WORKS, new String[] { TABLE_ROW_ID, TABLE_ROW_PROJECT_ID, TABLE_ROW_DESCRIPTION, TABLE_ROW_DATE_FROM, TABLE_ROW_DATE_TO },
          TABLE_ROW_PROJECT_ID + "=" + projectID, null, null, null, null, null);
      cursor.moveToFirst();
      if (!cursor.isAfterLast()) {
        do {
          Work work = new Work(cursor.getInt(0), cursor.getLong(1), cursor.getString(2), cursor.getLong(3)*1000, cursor.getLong(4)*1000);
          works.add(work);
          Log.d(WWCLIENT, "$ " + work.toString() + " loaded from database");
        } while (cursor.moveToNext());
      }
      cursor.close();

      // get works from table works
      cursor = db.query(TABLE_WORKS, new String[] { TABLE_ROW_ID, TABLE_ROW_PROJECT_ID, TABLE_ROW_DESCRIPTION, TABLE_ROW_DATE_FROM, TABLE_ROW_DATE_TO }, TABLE_ROW_PROJECT_ID + "="
          + projectID, null, null, null, null, null);
      cursor.moveToFirst();
      if (!cursor.isAfterLast()) {
        do {
          Work work = new Work(cursor.getInt(0), cursor.getLong(1), cursor.getString(2), cursor.getLong(3)*1000, cursor.getLong(4)*1000);
          works.add(work);
          Log.d(WWCLIENT, "$ " + work.toString() + " loaded from database");
        } while (cursor.moveToNext());
      }
      cursor.close();
    } catch (SQLException e) {
      Log.e(WWCLIENT, DB_ERROR + e.toString());
      e.printStackTrace();
    }
    Log.d(WWCLIENT, "$ ... " + works.size() + " works loaded from database");
    return works;
  }

  /**
     * 
     */
  public List<Work> getTodayWorks(long projectID) {
    List<Work> works = new ArrayList<Work>();
    Cursor cursor;
    try {
      // get works from NOT SENT queue
      cursor = db.query(TABLE_NOT_SENT_WORKS, new String[] { TABLE_ROW_ID, TABLE_ROW_PROJECT_ID, TABLE_ROW_DESCRIPTION, TABLE_ROW_DATE_FROM, TABLE_ROW_DATE_TO },
          TABLE_ROW_PROJECT_ID + "=" + projectID + " AND " + TABLE_ROW_DATE_FROM + ">=" + WWLogic.getLastMidnightMillis()/1000, null, null, null, null, null);
      cursor.moveToFirst();
      if (!cursor.isAfterLast()) {
        do {
          Log.d("WWCLIENT", "### " + cursor.getInt(3));
          Work work = new Work(cursor.getInt(0), cursor.getLong(1), cursor.getString(2), cursor.getLong(3)*1000, cursor.getLong(4)*1000);
          works.add(work);
          Log.d(WWCLIENT, "$ " + work.toString() + " loaded from database");
        } while (cursor.moveToNext());
      }
      cursor.close();

      // get works from table works
      cursor = db.query(TABLE_WORKS, new String[] { TABLE_ROW_ID, TABLE_ROW_PROJECT_ID, TABLE_ROW_DESCRIPTION, TABLE_ROW_DATE_FROM, TABLE_ROW_DATE_TO }, TABLE_ROW_PROJECT_ID + "="
          + projectID + " AND " + TABLE_ROW_DATE_FROM + ">=" + WWLogic.getLastMidnightMillis()/1000, null, null, null, null, null);
      cursor.moveToFirst();
      if (!cursor.isAfterLast()) {
        do {
          Work work = new Work(cursor.getInt(0), cursor.getLong(1), cursor.getString(2), cursor.getLong(3)*1000, cursor.getLong(4)*1000);
          works.add(work);
          Log.d(WWCLIENT, "$ " + work.toString() + " loaded from database");
        } while (cursor.moveToNext());
      }
      cursor.close();
    } catch (SQLException e) {
      Log.e(WWCLIENT, DB_ERROR + e.toString());
      e.printStackTrace();
    }
    Log.d(WWCLIENT, "$ ... " + works.size() + " today works loaded from database");
    return works;
  }

  public List<Work> getNotSentWorks() {
    List<Work> notSentWorks = new ArrayList<Work>();
    Cursor cursor;

    try {
      cursor = db.query(TABLE_NOT_SENT_WORKS, new String[] { TABLE_ROW_ID, TABLE_ROW_PROJECT_ID, TABLE_ROW_DESCRIPTION, TABLE_ROW_DATE_FROM, TABLE_ROW_DATE_TO }, TABLE_ROW_DATE_TO
          + ">" + 0, null, null, null, null, null);
      cursor.moveToFirst();
      if (!cursor.isAfterLast()) {
        do {
          Work work = new Work(cursor.getLong(0), cursor.getLong(1), cursor.getString(2), cursor.getLong(3)*1000, cursor.getLong(4)*1000);
          notSentWorks.add(work);
          Log.d(WWCLIENT, "$ " + work.toString() + " loaded from database");
        } while (cursor.moveToNext());
      }
      cursor.close();
    } catch (SQLException e) {
      Log.e(WWCLIENT, DB_ERROR + e.toString());
      e.printStackTrace();
    }
    Log.d(WWCLIENT, "$ ... " + notSentWorks.size() + " not sent works loaded from database");
    return notSentWorks;
  }

  public int deleteWork(long workID) {
    try {
      int returnCode = db.delete(TABLE_NOT_SENT_WORKS, TABLE_ROW_ID + "=" + workID, null);
      Log.d(WWCLIENT, "$ Work with ID " + workID + " deleted from database");
      return returnCode;
    } catch (Exception e) {
      Log.e(WWCLIENT, DB_ERROR + e.toString());
      e.printStackTrace();
    }
    return 0;
  }

  public void addWork(Work work) {
    if (work != null) {
      ContentValues values = new ContentValues();
      values.put(TABLE_ROW_ID, work.getId());
      values.put(TABLE_ROW_PROJECT_ID, work.getProjectID());
      values.put(TABLE_ROW_DESCRIPTION, work.getDescription());
      values.put(TABLE_ROW_DATE_FROM, work.getFrom()/1000);
      values.put(TABLE_ROW_DATE_TO, work.getTo()/1000);
      
      Log.d("WWCLIENT", "### " + work.getFrom());
      
      try {
        db.insert(TABLE_WORKS, null, values);
        Log.d(WWCLIENT, "$ " + work.toString() + " saved to database");
      } catch (Exception e) {
        Log.e(WWCLIENT, DB_ERROR + e.toString());
        e.printStackTrace();
      }
    }
  }

  public int addListOfWorks(List<Work> listOfWorks) {
    int count = 0;
    for (Work work : listOfWorks) {
      addWork(work);
      count++;
    }
    Log.d(WWCLIENT, "$ ... " + count + " works saved to database");
    return count;
  }

  /**
   * This inner class is designed to check if there is a database that currently
   * exists for the given program. If the database does not exist, it creates
   * one. After the class ensures that the database exists, this class will open
   * the database for use. Most of this functionality will be handled by the
   * SQLiteOpenHelper parent class. The purpose of extending this class is to
   * tell the class how to create (or update) the database.
   */
  private class CustomSQLiteOpenHelper extends SQLiteOpenHelper {

    public CustomSQLiteOpenHelper(Context context) {
      super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
      // the SQLite query string that will create database table
      String projectsTableQueryString = "create table " + TABLE_PROJECTS + " (" + TABLE_ROW_ID + " integer primary key not null, " + TABLE_ROW_NAME + " text, " + TABLE_ROW_OPEN
          + " integer" + ");";
      db.execSQL(projectsTableQueryString);

      String worksTableQueryString = "create table " + TABLE_WORKS + " (" + TABLE_ROW_ID + " integer primary key not null, " + TABLE_ROW_PROJECT_ID + " integer, "
          + TABLE_ROW_DESCRIPTION + " text, " + TABLE_ROW_DATE_FROM + " integer, " + TABLE_ROW_DATE_TO + " integer" + ");";
      db.execSQL(worksTableQueryString);

      String notSentWorksTableQueryString = "create table " + TABLE_NOT_SENT_WORKS + " (" + TABLE_ROW_ID + " integer primary key autoincrement not null, " + TABLE_ROW_PROJECT_ID
          + " integer, " + TABLE_ROW_DESCRIPTION + " text, " + TABLE_ROW_DATE_FROM + " integer, " + TABLE_ROW_DATE_TO + " integer default 0" + ");";
      db.execSQL(notSentWorksTableQueryString);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
      // NOTHING TO DO HERE. THIS IS THE ORIGINAL DATABASE VERSION.
      // OTHERWISE, YOU WOULD SPECIFIY HOW TO UPGRADE THE DATABASE
      // FROM OLDER VERSIONS.
    }
  }
}
